<?php 
class Mem_m extends Model {

    function Mem_m()
    {
        parent::Model();
		$cf = $this->member->memconfig();
    }
	
	//update avatar of user
	function updateUserAvatar($filename,$userid){
		$cf = $this->pgclass->memconfig();
		$s  = "UPDATE ".$cf['table_userprofile']." SET p_avatar='".$filename."' WHERE usrid=".$userid;
		$s  = mysql_query($s);
	}
	
	//report count member in period
	function countPeriod(){
		$cf = $this->member->memconfig();
		// today //
		$today		= "SELECT count(*) as 'today' FROM ".$cf['table_userprofile']." WHERE (signupdate < DATE_ADD(CURDATE(), INTERVAL +1 DAY) AND  signupdate>DATE_ADD(CURDATE(), INTERVAL 0 DAY))";
		$today		= mysql_query($today);
		$today 		= mysql_fetch_assoc($today);
		$data['today']	= $today['today'];
		// yesterday //
		$yesterday	= "SELECT count(*) as 'yesterday' FROM ".$cf['table_userprofile']." WHERE (signupdate < DATE_ADD(CURDATE(), INTERVAL 0 DAY) AND  signupdate>DATE_ADD(CURDATE(), INTERVAL -1 DAY))";
		$yesterday 	= mysql_query($yesterday);
		$yesterday 		= mysql_fetch_assoc($yesterday);
		$data['yesterday']	= $yesterday['yesterday'];
		// this month //
		$thismonth 	= "SELECT count(*) as 'thismonth' FROM ".$cf['table_userprofile']." WHERE MONTH(signupdate) = MONTH(NOW())";
		$thismonth 	= mysql_query($thismonth);
		$thismonth 	= mysql_fetch_assoc($thismonth);
		$data['thismonth']	= $thismonth['thismonth'];
		// last month //
		$lastmonth 	= "SELECT count(*) as 'lastmonth' FROM ".$cf['table_userprofile']." WHERE MONTH(signupdate) = MONTH(DATE_ADD(CURDATE(), INTERVAL -1 MONTH)) AND YEAR(signupdate) = YEAR(NOW())";
		$lastmonth 	= mysql_query($lastmonth);
		$lastmonth 	= mysql_fetch_assoc($lastmonth);
		$data['lastmonth']	= $lastmonth['lastmonth'];
		// this year //
		$thisyear	= "SELECT count(*) as 'thisyear' FROM ".$cf['table_userprofile']." WHERE YEAR(signupdate) = YEAR(NOW())";
		$thisyear 	= mysql_query($thisyear);
		$thisyear 	= mysql_fetch_assoc($thisyear);
		$data['thisyear']	= $thisyear['thisyear'];
		// last year //
		$lastyear	= "SELECT count(*) as 'lastyear' FROM ".$cf['table_userprofile']." WHERE YEAR(signupdate) = YEAR(DATE_ADD(CURDATE(), INTERVAL -1 YEAR))";
		$lastyear 	= mysql_query($lastyear);
		$lastyear 	= mysql_fetch_assoc($lastyear);
		$data['lastyear']	= $lastyear['lastyear'];
		
		return $data;
	}
	
	///report count member
	function countMemberReport(){
		$cf = $this->member->memconfig();
		$s = "SELECT count(*) as 'count',userstatus FROM ".$cf['table_usersystem']." GROUP BY userstatus";
		$s = mysql_query($s);
		return $s;
	}
	
	
	//Update Profile for active
	function updateUserActive($data,$userid){
		$cf = $this->member->memconfig();
		foreach($data as $k => $v)
		{
			if($k == 'p_birthdate') $v = $this->bee->datetodatabase($v);
			$res[] = '`'.$k.'` = \''.mysql_escape_string($v).'\'';
		}
		$signup = "UPDATE ".$cf['table_userprofile']." SET ".implode(', ', $res)." WHERE usrid=".$userid;
		mysql_query($signup) or die(mysql_error());
	}
	
	//search member
	function searchMember($keyword){
		$cf = $this->member->memconfig();
		$s = "SELECT ".$cf['table_usersystem'].".*,".$cf['table_userprofile'].".signupdate,".$cf['table_userprofile'].".p_name FROM ".$cf['table_usersystem']." JOIN ".$cf['table_userprofile']." ON ".$cf['table_usersystem'].".id=".$cf['table_userprofile'].".usrid 
		WHERE ".$cf['table_usersystem'].".username LIKE '%".$keyword."%' OR
		 ".$cf['table_usersystem'].".email LIKE '%".$keyword."%' OR
		 ".$cf['table_userprofile'].".p_name LIKE '%".$keyword."%' OR
		 ".$cf['table_userprofile'].".p_surname LIKE '%".$keyword."%' OR
		 ".$cf['table_userprofile'].".p_telephone LIKE '%".$keyword."%' OR
		 ".$cf['table_userprofile'].".p_mobilephone LIKE '%".$keyword."%' OR
		 ".$cf['table_userprofile'].".p_personalid LIKE '%".$keyword."%' ORDER BY id DESC";
		$data['usersystem'] = mysql_query($s);
		
		return $data['usersystem'];
	}
	
	//update status from wait to member to active member
	function activeMember($userid){
		$cf = $this->member->memconfig();
		$s = "UPDATE ".$cf['table_usersystem']." SET userstatus=90 WHERE id=".$userid;
		$s = mysql_query($s);
	}
	
	//check ว่าเป็นสมาชิก ที่รอการ activeจริง
	function checkWaitActive($userid){
		$cf = $this->member->memconfig();
		$s 	= "SELECT username FROM ".$cf['table_usersystem']." WHERE id=".$userid." AND userstatus=50";
		$s 	= mysql_query($s);
		if(mysql_num_rows($s)>0){
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
	
	//add user for wait to activate
	function addUserActivate($data){
		$cf = $this->member->memconfig();
		
		//insert into table_usersystem
		$data['password'] = $this->authen->hashpass($data['password']); //hash password
		$s = "INSERT INTO ".$cf['table_usersystem']." SET username='".$data['username']."', password='".$data['password']."', email='".$data['email']."', userstatus=50,userrank=".$data['userrank']."";
		$s = mysql_query($s);
		$data['usrid'] = mysql_insert_id();
		$signup = "INSERT INTO ".$cf['table_userprofile']." SET usrid=".$data['usrid'].",signupdate=NOW()";
		mysql_query($signup) or die(mysql_error());
		if($data['usrid']=='1'){
			//set to admin
			$s = "UPDATE ".$cf['table_usersystem']." SET userrank=90,userstatus=90 WHERE id=1";
			$s = mysql_query($s);
		}
		return $data['usrid'];
	}
	
	//load 50 list member
	function a_50mem($start,$limit){
		$cf = $this->member->memconfig();
		$s = "SELECT ".$cf['table_usersystem'].".*,".$cf['table_userprofile'].".signupdate,".$cf['table_userprofile'].".p_name FROM ".$cf['table_usersystem']." JOIN ".$cf['table_userprofile']." ON ".$cf['table_usersystem'].".id=".$cf['table_userprofile'].".usrid ORDER BY id DESC LIMIT $start,$limit";
		$data['usersystem'] = mysql_query($s);
		
		return $data['usersystem'];
	}
	
	//Load All Profile of user
	function a_loadProfile($id){
		$cf = $this->member->memconfig();
		$s = "SELECT * FROM ".$cf['table_usersystem']." WHERE id=".$id;
		$s = mysql_query($s);
		$data['memsystem']	= mysql_fetch_assoc($s);
		
		// select profile
		$a = "SELECT * FROM ".$cf['table_userprofile']." WHERE usrid=".$id;
		$a = mysql_query($a);
		$data['memprofile']	= mysql_fetch_assoc($a);
		
		return $data;
	}
	
	//count all member
	function countMember(){
		$cf = $this->member->memconfig();
		$s = "SELECT count(*) as 'cnt' FROM ".$cf['table_usersystem']." WHERE userrank != 90";
		$s = mysql_query($s);
		$s = mysql_fetch_assoc($s);
		return $s['cnt'];
	}
	
	//Delete User
	function deleteUser($user){
		//delete profile
		$cf = $this->member->memconfig();
		$s = "DELETE FROM ".$cf['table_userprofile']." WHERE usrid=".$user['userid']." AND usrid!=1";
		$s = mysql_query($s);
		
		//delete usersystem
		$s = "DELETE FROM ".$cf['table_usersystem']." WHERE id=".$user['userid']." AND id!=1";
		$s = mysql_query($s);
	}
	
	//Update password
	function updatePassword($data,$mem){
		//check old password
		$cf = $this->member->memconfig();
		$this->load->library('Authen');
		$data['oldpassword'] = $this->authen->hashpass($data['oldpassword']); //hash password
		$s = "SELECT username FROM ".$cf['table_usersystem']." WHERE password='".$data['oldpassword']."' AND id=".$mem['userid'];
		$s = mysql_query($s);
		if(mysql_num_rows($s)>0){
			//update to new password
			$data['password'] = $this->authen->hashpass($data['password']); //hash password
			$s = "UPDATE ".$cf['table_usersystem']." SET password='".$data['password']."' WHERE id=".$mem['userid']." AND username='".$mem['username']."'";
			$s = mysql_query($s);
			return TRUE;
		}
		else
		{
			//false old password
			return FALSE;
		}
	}
	
	//Update Profile
	function updateProfile($data,$userid){
		$cf = $this->member->memconfig();
		$data['p_birthdate'] = $this->bee->datetodatabase($data['p_birthdate']);
		foreach($data as $k => $v)
		{
			$res[] = '`'.$k.'` = \''.mysql_escape_string($v).'\'';
		}
		$signup = "UPDATE ".$cf['table_userprofile']." SET ".implode(', ', $res)." WHERE usrid=".$userid;
		mysql_query($signup) or die(mysql_error());
	}
	//update to remove avatar
	function removeimage($userid){
		$cf = $this->member->memconfig();
		$s 	= "UPDATE ".$cf['table_userprofile']." SET p_avatar='' WHERE usrid=".$userid;
		$s  = mysql_query($s);
		
	}
	
	//get avatar from userid
	function getimage($userid){
		$cf = $this->member->memconfig();
		$s = "SELECT p_avatar FROM ".$cf['table_userprofile']." WHERE usrid=".$userid;
		$s = mysql_query($s);
		$s = mysql_fetch_assoc($s);
		return $s['p_avatar'];
	}
	
	//Load profile for edit
	function profileEdit($userid){
		// select profile
		$cf = $this->member->memconfig();
		$a 		= "SELECT * FROM ".$cf['table_userprofile']." WHERE usrid=".$userid;
		$a 		= mysql_query($a);
		$data	= mysql_fetch_assoc($a); 
		
		return $data;
	}
	
	//Load All Profile of user
	function loadProfile($user){
		$cf = $this->member->memconfig();
		$s = "SELECT * FROM ".$cf['table_usersystem']." WHERE username='".$user['username']."' AND id=".$user['userid'];
		$s = mysql_query($s);
		$data['memsystem']	= mysql_fetch_assoc($s);
		
		// select profile
		$a = "SELECT * FROM ".$cf['table_userprofile']." WHERE usrid=".$user['userid'];
		$a = mysql_query($a);
		$data['memprofile']	= mysql_fetch_assoc($a);
		
		return $data;
	}
	
	//forgot password from database 
	function forgotPassword($email)
	{
		$cf = $this->member->memconfig();
		$s 	= "SELECT username FROM ".$cf['table_usersystem']." WHERE email='".$email."'";
		$s 	= mysql_query($s);
		if(mysql_num_rows($s)>0){
			$data[0] = TRUE;
			$s = mysql_fetch_assoc($s);
			$data[1] = $s['username'];
		}
		else
		{
			$data[0] = FALSE;
		}
		return $data;
	}
	
	//get userid FROM email
	function useridFromEmail($email){
		$cf		= $this->member->memconfig();
		$s = "SELECT id,username FROM ".$cf['table_usersystem']." WHERE email='".$email."'";
		$s = mysql_query($s);
		$s = mysql_fetch_assoc($s);
		return $s;
	}
	
	//get userid FROM username
	function useridFromUser($username){
		$cf		= $this->member->memconfig();
		$s = "SELECT id FROM ".$cf['table_usersystem']." WHERE username='".$username."'";
		$s = mysql_query($s);
		$s = mysql_fetch_assoc($s);
		return $s['id'];
	}
	//signup username
	function signupUser($data){
		$this->load->library('Authen');
		$this->load->library('Bee');
		$cf		= $this->member->memconfig();

		//insert into table_usersystem
		$data['password'] = $this->authen->hashpass($data['password']); //hash password
		$s = "INSERT INTO ".$cf['table_usersystem']." SET username='".$data['username']."', password='".$data['password']."', email='".$data['email']."', userstatus=90,userrank=60";
		$s = mysql_query($s);
		$data['usrid'] = mysql_insert_id();
		unset($data['confirmpassword'],$data['username'],$data['password'],$data['email']);
		
		foreach($data as $k => $v)
		{
			if($k =='p_birthdate') $v = $this->bee->datetodatabase($v);
			$res[] = '`'.$k.'` = \''.mysql_escape_string($v).'\'';
		}
		$signup = "INSERT INTO ".$cf['table_userprofile']." SET ".implode(', ', $res).",signupdate=NOW()";
		mysql_query($signup) or die(mysql_error());
		if($data['usrid']=='1'){
			//set to admin
			$s = "UPDATE ".$cf['table_usersystem']." SET userrank=90,userstatus=90 WHERE id=1";
			$s = mysql_query($s);
		}
	}
	
	//check username duplicate
	function checkUsernamedup($user){
		$this->load->library('Member');
		$cf		= $this->member->memconfig();
		$s = "SELECT username FROM ".$cf['table_usersystem']." WHERE username='".$user."'";
		$s = mysql_query($s);
		if(mysql_num_rows($s)>0){
			return TRUE;
		}	
		else
		{
			return FALSE;
		}
	}
	
	//check email duplicate
	function checkEmaildup($email){
		$this->load->library('Member');
		$cf		= $this->member->memconfig();
		$s = "SELECT email FROM ".$cf['table_usersystem']." WHERE email='".$email."'";
		$s = mysql_query($s);
		if(mysql_num_rows($s)>0){
			return TRUE;
		}	
		else
		{
			return FALSE;
		}
	}
	
	/// ค้นอำเภอ จากจังหวัด
	// INPUT : $city{NUM},$id{NUM}
	// OUTPUT: mysql resource || Array || text
	function getAmphur($city,$id){
		if($city =='0' && $id == '0'){
			$s = "SELECT * FROM amphur ORDER BY amphur_id";
			$s = mysql_query($s);
			return $s;
		}
		elseif($city != '0' && $id =='0'){
			$s = "SELECT amphur_name FROM amphur WHERE province_id=".$city."";
			$s = mysql_query($s);
			$cnt = 0;
			while($t = mysql_fetch_assoc($s)){
				$c[$cnt] = $t['amphur_name'];
			}
			return $c;
		}
		elseif($city == '0' && $id != '0'){
			$s = "SELECT amphur_name FROM amphur WHERE amphur_id=".$id."";
			$s = mysql_query($s);
			$s = mysql_fetch_assoc($s);
			return $s['amphur_name'];
		}
		else
		{
			return FALSE;
		}
	}
	
	//check login by username
	function loginUser($user,$pass){
		$cf		= $this->member->memconfig();
		$pass 	= $this->authen->hashpass($pass);
		$s = "SELECT username FROM ".$cf['table_usersystem']." WHERE username='".$user."' AND password='".$pass."' AND userstatus !=20";
		$s = mysql_query($s);
		if(mysql_num_rows($s)>0){
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
	
	//check login by email
	function loginEmail($email,$pass){
		$cf		= $this->member->memconfig();
		$pass 	= $this->authen->hashpass($pass);
		$s = "SELECT username FROM ".$cf['table_usersystem']." WHERE email='".$email."' AND password='".$pass."' AND userstatus !=20";
		$s = mysql_query($s) OR DIE(mysql_query($s));
		if(mysql_num_rows($s)>0){
			return TRUE;
		}
		else
		{
			return FALSE;
		}
	}
}

?>